package com.itmate.chenshiguang.dao;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.itmate.chenshiguang.dao.BaseDao;
import com.itmate.chenshiguang.db.DBUtil;
import com.itmate.chenshiguang.entity.FamilyEntity;




public class FamilyDao extends BaseDao{
	
	public int addFamily(FamilyEntity f){
		int count = 0;
	 try {
		connection = DBUtil.getConnection();
		String sql = "insert into family(id,name) values(?,?)";
		preparedStatement = connection.prepareStatement(sql);
		preparedStatement.setInt(1, f.getId());
		preparedStatement.setString(2, f.getName());
		count = preparedStatement.executeUpdate();
	} catch (Exception e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}finally{
		closed();
	}
	return count;
		
	}
	
	public int deleteFamily(int id){
		int i=-1;
		try {
			connection=DBUtil.getConnection();
			preparedStatement=connection.prepareStatement(
					"delete from family where id=?");
			preparedStatement.setInt(1, id);
			i =preparedStatement.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			closed();
		}
		return i;
	}
	
	public List<FamilyEntity> selectAllFamily(){
		List<FamilyEntity> FamilyList= new ArrayList<>();
		try {
		 	
			connection = DBUtil.getConnection();
			preparedStatement=connection.prepareStatement("select * from family ");
			statement = connection.createStatement();
			resultSet=preparedStatement.executeQuery();
			while(resultSet.next()){
				FamilyEntity f = new FamilyEntity();
				f.setId(resultSet.getInt("id"));
				f.setName(resultSet.getString("name"));
				FamilyList.add(f);
			}
			
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closed();
		}
		return FamilyList;
	}
	
	public int updateFamily(FamilyEntity f){
		int i=-1;
		try {
		connection=DBUtil.getConnection();
		preparedStatement=connection.prepareStatement("update family set name =? where id=?");
		preparedStatement.setString(1, f.getName());
		preparedStatement.setInt(2, f.getId());
		i=preparedStatement.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return i;
	}
	public List<FamilyEntity> selectFamily(String name){
		List FamilyList = new ArrayList<FamilyEntity>();
		try {
			connection=DBUtil.getConnection();
			preparedStatement=connection.prepareStatement("select * from family where name like ?");
			preparedStatement.setString(1, "%"+name+"%");
			resultSet=preparedStatement.executeQuery();
			while(resultSet.next()){
				FamilyEntity f = new FamilyEntity();
				String name1  = resultSet.getString("name");
				
				f.setName(name1);
				FamilyList.add(f);

			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closed();
		}
		return FamilyList;
	}

		
	}



